﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class MVTVKEKData : BaseFunction
    {
        
        public MVTVKEKData()
            : base()
        {

        }
        public MVTVKEKData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKEK(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VKEK";
            string PI = dwABPOS["PI"].ToString().Trim();
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string SUP = dwABPOS["SUP"].ToString().Trim();
            string KUNDNR = dwABPOS["KUNDNR"].ToString().Trim();
            string NEUBEN = dwABPOS["NEUBEN"].ToString().Trim();
            string SACHBE = dwABPOS["SACHBE"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            string FROMEKDATE = dwABPOS["FROMEKDATE"].ToString().Trim();
            string ENDEKDATE = dwABPOS["ENDEKDATE"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT A.VKPAUFKRS||'-'||TO_NUMBER(A.VKPAUFLFD)||'-'||TRIM(D.KDKUNDNA) AS PI, "
                    + " A.VKPAUFPOS,A.VKPKDKOMM,A.VKPART,A.VKPFARBE,A.VKPMENGE1*0.01 AS VKQTY,A.VKPMEEH,A.VKPLIEFTERM, "
                    + " F.LSLIEFNA,C.EKPLSART,C.EKPLSFB,C.EKPME1*0.01 AS EKQTY,C.EKPMEEH,C.EKPWUTERM,C.EKPNEUD, "
                    + " C.EKPLTERM,E.VKKANSPZUS,TRIM(D.KDKUNDNA) AS KUNDNR,E.VKKNEUBEN,E.VKKSACHBE,  "
                    + " to_date(C.EKPLTERM,'YYYYMMDD')-TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD') AS DD "
                    + " from VKPOS A,ABAUFM B,EKPOS C,KDSTAMM D,VKKOPF E,LIEFDAT F "
                    + " WHERE A.VKPMANDANT = B.ABAMANDANT "
                    + " AND A.VKPAUFKRS = B.ABAVKAUFKRS AND A.VKPAUFLFD = B.ABAVKAUFLFD "
                    + " AND A.VKPAUFPOS = B.ABAVKAUFPOS AND A.VKPSA <> 'AJ'  AND A.VKPERL <>'J' "
                    + " AND B.ABAMANDANT = C.EKPMANDANT AND B.ABAAUFKRS = C.EKPAUFKRS "
                    + " AND B.ABAAUFLFD = C.EKPAUFLFD AND B.ABAAUFPOS = C.EKPAUFPOS "
                    + " AND C.EKPSA = 'PO' AND A.VKPMANDANT = D.KDMANDANT "
                    + " AND A.VKPKUNDNR = D.KDKUNDNR AND A.VKPMANDANT = E.VKKMANDANT "
                    + " AND A.VKPAUFKRS = E.VKKAUFKRS AND A.VKPAUFLFD = E.VKKAUFLFD "
                    + " AND E.VKKSA <>'AJ' AND C.EKPMANDANT = F.LSMANDANT AND C.EKPLIEFNR = F.LSLIEFNR "
                    + " AND (A.VKPAUFKRS||'-'||TO_NUMBER(A.VKPAUFLFD)||'-'||TRIM(D.KDKUNDNA) like '" + PI + "%' OR '" + PI + "' = 'AAA' ) "
                    + " AND (trim(A.VKPART) = '" + ARTICLE + "' OR '" + ARTICLE + "' = 'AAA' ) "
                    + " AND (trim(F.LSLIEFNA) = '" + SUP + "' OR '" + SUP + "' = 'AAA' ) "
                    + " AND (trim(D.KDKUNDNA) = '" + KUNDNR + "' OR '" + KUNDNR + "' = 'AAA' ) "
                    + " AND (trim(E.VKKNEUBEN) = '" + NEUBEN + "' OR '" + NEUBEN + "' = 'AAA' ) "
                    + " AND (trim(E.VKKSACHBE) = '" + SACHBE + "' OR '" + SACHBE + "' = 'AAA' ) "
                    + " AND (A.VKPLIEFTERM >= " + FROMDATE + " AND  A.VKPLIEFTERM <= " + ENDDATE + ") "
                    + " AND (C.EKPLTERM >= " + FROMDNDATE + " AND  C.EKPLTERM <= " + ENDDNDATE + ") "
                    + " AND (C.EKPNEUD >= " + FROMEKDATE + " AND  C.EKPNEUD <= " + ENDEKDATE + ") "
                    + " UNION ALL "
                    + " SELECT DISTINCT A.VKPAUFKRS || '-' || TO_NUMBER(A.VKPAUFLFD) || '-' || "
                    + " TRIM(D.KDKUNDNA) AS PI,A.VKPAUFPOS,A.VKPKDKOMM,A.VKPART,A.VKPFARBE, "
                    + " A.VKPMENGE1 * 0.01 AS VKQTY,A.VKPMEEH,A.VKPLIEFTERM,'','','',0,'',0,0,0, "
                    + " E.VKKANSPZUS,TRIM(D.KDKUNDNA) AS KUNDNR,E.VKKNEUBEN,E.VKKSACHBE, "
                    + " 0 AS DD "
                    + " FROM VKPOS A, KDSTAMM D, VKKOPF E "
                    + " WHERE A.VKPSA <> 'AJ' AND A.VKPERL <>'J' "
                    + " AND A.VKPMANDANT = D.KDMANDANT AND A.VKPKUNDNR = D.KDKUNDNR "
                    + " AND A.VKPMANDANT = E.VKKMANDANT AND A.VKPAUFKRS = E.VKKAUFKRS "
                    + " AND A.VKPAUFLFD = E.VKKAUFLFD AND E.VKKSA <> 'AJ' "
                    + " AND NOT EXISTS (SELECT 1 FROM ABAUFM B WHERE A.VKPMANDANT = B.ABAMANDANT "
                    + " AND A.VKPAUFKRS = B.ABAVKAUFKRS AND A.VKPAUFLFD = B.ABAVKAUFLFD "
                    + " AND A.VKPAUFPOS = B.ABAVKAUFPOS) "
                    + " AND (A.VKPAUFKRS||'-'||TO_NUMBER(A.VKPAUFLFD)||'-'||TRIM(D.KDKUNDNA) like '" + PI + "%' OR '" + PI + "' = 'AAA' ) "
                    + " AND (trim(A.VKPART) = '" + ARTICLE + "' OR '" + ARTICLE + "' = 'AAA' ) "
                    + " AND (trim(D.KDKUNDNA) = '" + KUNDNR + "' OR '" + KUNDNR + "' = 'AAA' ) "
                    + " AND (trim(E.VKKNEUBEN) = '" + NEUBEN + "' OR '" + NEUBEN + "' = 'AAA' ) "
                    + " AND (trim(E.VKKSACHBE) = '" + SACHBE + "' OR '" + SACHBE + "' = 'AAA' ) "
                    + " AND (A.VKPLIEFTERM >= " + FROMDATE + " AND  A.VKPLIEFTERM <= " + ENDDATE + " ) "
                    + " ORDER BY KUNDNR,1,2 "
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["EKPLSART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["EKPLSART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["EKPLSFB"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["EKPLSFB"] = value;
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        
        public DataSet BuildMVTVKEKData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKEK(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}